TD Récapitulatif SQL

TD révision générale

WarningAvec solutions

L3 MIASHS

Université Paris Cité

Année 2025

Course Homepage

Moodle

Recapitulatif SQL

Utiliser les outils les plus adaptés parmi ceux vus depuis le début de l’année pour répondre aux questions suivantes.

Schéma world

NoteQuestion

Quel est le pays qui a le plus de villes recensées dans la table world.city? Combien de villes ? (363 villes)

TipSolution

WITH country_city AS
    (SELECT name_country , COUNT(name) nbrcity
    FROM world.country NATURAL JOIN world.city
    GROUP BY name_country)

SELECT name_country, nbrcity
FROM country_city
WHERE nbrcity = (SELECT MAX(nbrcity) 
                 FROM country_city) ;

NoteQuestion

Quels sont les pays qui ont \(3\) ou plus langues officielles ? (8 lignes)

TipSolution

SELECT name_country
FROM world.country NATURAL JOIN world.countrylanguage
WHERE isofficial
GROUP BY name_country HAVING COUNT(*) > 2 ;

NoteQuestion

Quels sont les pays dont plus de \(1.000.000\) d’habitants parlent une langue non officielle ? (72 lignes)

TipSolution

SELECT DISTINCT name_country
FROM world.country NATURAL JOIN world.countrylanguage
WHERE (NOT isofficial) AND 
      (percentage / 100) * population_country >= 1000000 ;

NoteQuestion

Quelles sont les langues qui sont officielles dans tous les continents où au moins une langue est parlée ? (1 ligne)

TipSolution

WITH R AS
    (SELECT count(DISTINCT continent)
    FROM world.country NATURAL JOIN world.countrylanguage)
SELECT language, count(DISTINCT continent) as nbcon
FROM world.country NATURAL JOIN world.countrylanguage WHERE isofficial
GROUP BY language
    HAVING count(DISTINCT continent) in (SELECT * FROM R)
ORDER BY nbcon DESC ;

ou

TipSolution

WITH continent_language AS
    (SELECT DISTINCT language , continent
    FROM country NATURAL JOIN countrylanguage
    WHERE isofficial),
all_continent_language AS
    (SELECT DISTINCT l.language , c.continent
    FROM countrylanguage l , continent_language c),
not_continent_language AS
    ((SELECT * FROM all_continent_language)
    EXCEPT
    (SELECT * FROM continent_language))
SELECT DISTINCT language
FROM countrylanguage
WHERE language NOT IN (SELECT language FROM not_continent_language) ;

NoteQuestion

Quels sont les pays pour lesquels une langue non officielle est parlée par strictement plus de monde (dans le pays) qu’il n’y a d’habitants dans la capitale ? (118 lignes)

TipSolution

SELECT DISTINCT name_country
FROM (country c JOIN city ON id = capital)
    JOIN countrylanguage l ON c.countrycode = l.countrycode
WHERE NOT isofficial
AND percentage / 100 * population_country > population ;

NoteQuestion

Quels est le nombre de villes par region ?

TipSolution

SELECT region , COUNT(id) AS nbr_city
FROM country NATURAL JOIN city
GROUP BY region ;

Schéma pagila

NoteQuestion

Quel est l’acteur (actor_id) présent sur le plus de DVD (inventory_id) ?

TipSolution

WITH actor_dvd AS
    (SELECT actor_id , count(inventory_id) nbrdvd
    FROM actor NATURAL JOIN film_actor NATURAL JOIN inventory
    GROUP BY actor_id)
SELECT first_name , last_name
FROM actor NATURAL JOIN actor_dvd
WHERE nbrdvd = (SELECT MAX(nbrdvd) FROM actor_dvd) ;

NoteQuestion

Quel est le prix moyen d’une location de DVD ?

TipSolution

SELECT AVG(amount) 
FROM payment ;

NoteQuestion

Quel est le DVD qui a rapporté le plus d’argent ? Le film (film_id)?

TipSolution

Pour le DVD:

WITH inventory_amount AS
    (SELECT inventory_id , SUM(amount) AS dollar
    FROM inventory NATURAL JOIN rental NATURAL JOIN payment
    GROUP BY inventory_id)
SELECT inventory_id , dollar
FROM  inventory_amount
WHERE dollar >= ALL (SELECT dollar FROM inventory_amount) ;

TipSolution

Et pour le film:

WITH film_amount AS
    (SELECT film_id , SUM(amount) AS dollar
    FROM inventory NATURAL JOIN rental NATURAL JOIN payment
    GROUP BY film_id)
SELECT film_id , dollar
FROM  inventory_amount
WHERE dollar >= ALL (SELECT dollar FROM film_amount) ;

NoteQuestion

Quels sont les employés (staff_id) qui travaillent dans une autre ville que celle dans laquelle ils vivent ? (4 lignes)

TipSolution

WITH staff_maison AS
    (SELECT staff_id , city_id
    FROM (staff NATURAL JOIN address)),
staff_travail AS
    (SELECT staff_id , city_id
    FROM (staff p JOIN store s ON p.store_id = s.store_id) , address a
    WHERE a.address_id = s.address_id)
SELECT staff_id FROM staff_travail NATURAL JOIN staff_maison ;